/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.artist.Album;
import DTO.artist.Albums;
import DTO.artist.Artist;
import DTO.artist.Artists;
import DTO.artist.Subscriber;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 *
 * @author tiwtiger
 */
public class ArtistDAO {

    public boolean add(Artist artist) {
        Connection con = null;
        PreparedStatement stm = null;
        PreparedStatement stmSubscriber = null;

        try {
            con = DBConnection.getConnection();
            // Insert table Artist
            stm = con.prepareStatement("INSERT INTO [Artists](ArtistName, Biology, ArtistImageSRC,"
                    + "SubscriberNumber, ArtistViews) VALUES(?,?,?,?,?)");
            stm.setString(1, artist.getArtistName());
            stm.setString(2, artist.getBiology());
            stm.setString(3, artist.getArtistImageSRC());
            stm.setInt(4, artist.getSubscriberNumber().intValue());
            stm.setInt(5, artist.getArtistViews().intValue());
            stm.executeUpdate();

            // Insert table ArtistSubscriber
            Subscriber subscriber = artist.getSubscribers();
            if (subscriber != null) {
                for (int i = 0; i < artist.getSubscriberNumber().intValue(); i++) {
                    stmSubscriber = con.prepareStatement("INSERT INTO [ArtistSubscriber](ArtistID, Username) "
                            + "VALUES(?,?)");
                    stmSubscriber.setInt(1, artist.getArtistID().intValue());
                    stmSubscriber.setString(2, subscriber.getUsername().get(i));
                    stmSubscriber.executeUpdate();
                }
            }

            return true;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            } else if (stmSubscriber != null) {
                try {
                    stmSubscriber.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean deleteArtist(int artistID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [Artists] WHERE [ArtistID]=?");
            stm.setInt(1, artistID);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean deleteSubscriber(int artistID, String username) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [ArtistSubscriber] WHERE [ArtistID]=? AND [Username]=?");
            stm.setInt(1, artistID);
            stm.setString(2, username);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean update(Artist artist) {
        Connection con = null;
        PreparedStatement stm = null;
        PreparedStatement stmSubscriber = null;

        try {
            boolean found;
            con = DBConnection.getConnection();
            // update table Artists
            stm = con.prepareStatement("UPDATE [Artists] SET ArtistName=?, Biology=?, ArtistImageSRC=?, "
                    + "SubscriberNumber=?, ArtistViews=? WHERE [ArtistID]=?");
            stm.setString(1, artist.getArtistName());
            stm.setString(2, artist.getBiology());
            stm.setString(3, artist.getArtistImageSRC());
            stm.setInt(4, artist.getSubscriberNumber().intValue());
            stm.setInt(5, artist.getArtistViews().intValue());
            stm.setInt(6, artist.getArtistID().intValue());
            stm.executeUpdate();

            //update table ArtistSubscriber
            ArtistDAO artistDAO = new ArtistDAO();

            Subscriber newSubscriber = artist.getSubscribers();
            if (newSubscriber != null) {
                Subscriber subscriber = artistDAO.selectSubscriberByArtistID(artist.getArtistID().intValue());
                for (int i = 0; i < subscriber.getUsername().size(); i++) {
                    found = false;
                    for (int j = 0; j < newSubscriber.getUsername().size(); j++) {
                        if (subscriber.getUsername().get(i).equals(newSubscriber.getUsername().get(j))) {
                            found = true;
                            break;
                        }
                    }
                    if (!found) {
                        deleteSubscriber(artist.getArtistID().intValue(), subscriber.getUsername().get(i));
                    }
                }
                // trong xml co' ma` database khong co.
                for (int i = 0; i < newSubscriber.getUsername().size(); i++) {
                    found = false;
                    for (int j = 0; j < subscriber.getUsername().size(); j++) {
                        if (newSubscriber.getUsername().get(i).equals(subscriber.getUsername().get(j))) {
                            found = true;
                            break;
                        }
                    }
                    if (!found) {
                        stmSubscriber = con.prepareStatement("INSERT INTO [ArtistSubscriber](ArtistID, Username) "
                                + "VALUES(?,?)");
                        stmSubscriber.setInt(1, artist.getArtistID().intValue());
                        stmSubscriber.setString(2, newSubscriber.getUsername().get(i));
                        stmSubscriber.executeUpdate();
                    }
                }
            }


            return true;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            } else if (stmSubscriber != null) {
                try {
                    stmSubscriber.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public Albums selectAlbumsByArtistID(int artistID) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * from [Albums] WHERE ArtistID=?");
            stm.setInt(1, artistID);
            ResultSet rs = stm.executeQuery();

            Albums albums = new Albums();
            Album album = null;
            while (rs.next()) {
                album = new Album();
                album.setAlbumID(rs.getBigDecimal("AlbumID").toBigInteger());
                album.setAlbumTitle(rs.getString("AlbumTitle"));
                album.setYear(rs.getBigDecimal("Year").toBigInteger());
                album.setAlbumViews(rs.getBigDecimal("AlbumViews").toBigInteger());
                album.setAlbumCoverSRC(rs.getString("AlbumCoverSRC"));
                albums.getAlbum().add(album);
            }
            return albums;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection Error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public Subscriber selectSubscriberByArtistID(int artistID) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * FROM [ArtistSubscriber] WHERE ArtistID=?");
            stm.setInt(1, artistID);
            ResultSet rs = stm.executeQuery();

            Subscriber sub = new Subscriber();
            while (rs.next()) {
                sub.getUsername().add(rs.getString("Username"));
            }
            return sub;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection Error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public Artists selectAllArtists() {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * FROM [Artists]");
            ResultSet rs = stm.executeQuery();

            Artists artists = new Artists();
            Artist artist = null;
            Albums albums = null;
            Subscriber sub = null;
            ArtistDAO artistDAO = new ArtistDAO();
            while (rs.next()) {
                artist = new Artist();
                artist.setArtistID(rs.getBigDecimal("ArtistID").toBigInteger());
                artist.setArtistName(rs.getString("ArtistName"));
                artist.setBiology(rs.getString("Biology"));
                artist.setArtistImageSRC(rs.getString("ArtistImageSRC"));
                artist.setSubscriberNumber(rs.getBigDecimal("SubscriberNumber").toBigInteger());
                artist.setArtistViews(rs.getBigDecimal("ArtistViews").toBigInteger());
                sub = artistDAO.selectSubscriberByArtistID(rs.getInt("ArtistID"));
                albums = artistDAO.selectAlbumsByArtistID(rs.getInt("ArtistID"));
                artist.setAlbums(albums);
                artist.setSubscribers(sub);
                artists.getArtist().add(artist);
            }
            return artists;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection Error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean SearchArtistByID(int artistID) {
        Connection con = null;
        PreparedStatement stm = null;
        Boolean found = false;

        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * FROM [ARTISTS] WHERE [ArtistID]=?");
            stm.setInt(1, artistID);

            ResultSet rs = stm.executeQuery();
            if (rs.next()) {
                found = true;
            }
            return found;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection Error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean saveAll(Artists artists) {
        for (int i = 0; i < artists.getArtist().size(); i++) {
            Artist artist = new Artist();
            artist = artists.getArtist().get(i);
            if (SearchArtistByID(artist.getArtistID().intValue())) {
                update(artist);
            } else {
                add(artist);
            }
        }
        return true;
    }
}
